package dao.impl;

import bean.Area;
import bean.ClassB;
import dao.IClassBDao;
import util.DBUTil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ClassBDaoImpl implements IClassBDao {
    @Override
    public List<ClassB> listByPage(int pageNo, int pageSize) {
        List<ClassB> classList =new ArrayList<>();
        Connection conn =null;
        PreparedStatement ps =null;
        ResultSet rs =null;

        try {
            conn = DBUTil.getConn();
            String sql ="select tc.pkid as tcid,tc.f_className,tc.f_leader,tc.f_tel," +
                    "  ta.pkid as taid,ta.f_areaName,ta.f_forestType,ta.f_landType,ta.f_advantageTreeType " +
                    "from t_class as tc inner join t_area as ta" +
                    " where tc.pkid=ta.pkid  limit ?,?";
            //    SELECT
//    t_class.pkid,
//    t_class.f_className,
//    t_class.f_leader,
//    t_class.f_tel,
//    t_class.fk_areaId
//            FROM
//    t_class
            ps =conn.prepareStatement(sql);
            ps.setInt(1,(pageNo-1)*pageSize);
            ps.setInt(2,pageSize);
            rs =ps.executeQuery();
            while(rs.next()){
                ClassB cb =null;
                Area area =null;
                int tcid = rs.getInt("tcid");
                String f_className = rs.getString("f_className");
                String f_leader = rs.getString("f_leader");
                String f_tel = rs.getString("f_tel");
                //区域
                int taid = rs.getInt("taid");
                String f_areaName = rs.getString("f_areaName");
                String f_forestType = rs.getString("f_forestType");
                String f_landType = rs.getString("f_landType");
                String f_advantageTreeType = rs.getString("f_advantageTreeType");


                cb =new ClassB(tcid,f_className,f_leader,f_tel);
                area =new Area(taid,f_areaName,f_forestType,f_landType,f_advantageTreeType);
                cb.setArea(area);
                classList.add(cb);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }
        return classList;
    }

    @Override
    public int getCount() {
        int count=0;
        Connection conn =null;
        PreparedStatement ps =null;
        ResultSet rs =null;
        try {
            conn =DBUTil.getConn();
            String sql ="select count(0) from t_class";
            ps =conn.prepareStatement(sql);
            rs =ps.executeQuery();
            if(rs.next()){
                count =rs.getInt("count(0)");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }
        return count;
    }

    @Override
    public int addOneClass(ClassB cb) {
        int count =-1;
        Connection conn =null;
        PreparedStatement ps =null;

//    SELECT
//    t_class.pkid,
//    t_class.f_className,
//    t_class.f_leader,
//    t_class.f_tel,
//    t_class.fk_areaId
//            FROM
//    t_class
        try {
            conn =DBUTil.getConn();
            String sql ="insert into t_class(pkid,f_className,f_leader,f_tel) values(?,?,?,?)";
            ps =conn.prepareStatement(sql);
            ps.setInt(1,cb.getPkid());
            ps.setString(2,cb.getF_className());
            ps.setString(3,cb.getF_leader());
            ps.setString(4,cb.getF_tel());
            count =ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUTil.close(null,ps,conn);
        }

        return count;
    }

    @Override
    public int updateOneClass(int id, String f_leader, String f_tel) {
        int count =-1;
        Connection conn =null;
        PreparedStatement ps =null;

        try {
            conn = DBUTil.getConn();
            String sql ="update t_class set f_leader=?,f_tel=? where pkid =?";
            ps =conn.prepareStatement(sql);
            ps.setString(1,f_leader);
            ps.setString(2,f_tel);
            ps.setInt(3,id);
            count =ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(null,ps,conn);
        }
        return count;
    }

    @Override
    public List<ClassB> listByPage(int pageNo, int pageSize, String f_className, String f_areaName) {
        List<ClassB> classList =new ArrayList<>();
        Connection conn =null;
        PreparedStatement ps =null;
        ResultSet rs =null;

        try {
            conn = DBUTil.getConn();
            String sql ="select tc.pkid as tcid,tc.f_className,tc.f_leader,tc.f_tel," +
                    "  ta.pkid as taid,ta.f_areaName,ta.f_forestType,ta.f_landType,ta.f_advantageTreeType " +
                    "from t_class as tc inner join t_area as ta" +
                    " where tc.pkid=ta.pkid  ";
            //    SELECT
            //    t_class.pkid,
            //    t_class.f_className,
            //    t_class.f_leader,
            //    t_class.f_tel,
            //    t_class.fk_areaId
            //            FROM
            //    t_class
            if(f_className!=null){
                sql+=" and f_className like '%"+f_className+"%' ";
            }
            if(f_areaName!=null){
                sql+=" and f_areaName like '%"+f_areaName+"%' ";
            }
            sql += " limit "+(pageNo-1)*pageSize+","+pageSize+" ";
            ps =conn.prepareStatement(sql);
//            ps.setInt(1,(pageNo-1)*pageSize);
//            ps.setInt(2,pageSize);
            rs =ps.executeQuery();
            while(rs.next()){
                ClassB cb =null;
                Area area =null;
                int tcid = rs.getInt("tcid");
                String f_className1 = rs.getString("f_className");
                String f_leader = rs.getString("f_leader");
                String f_tel = rs.getString("f_tel");
                //区域
                int taid = rs.getInt("taid");
                String f_areaName1 = rs.getString("f_areaName");
                String f_forestType = rs.getString("f_forestType");
                String f_landType = rs.getString("f_landType");
                String f_advantageTreeType = rs.getString("f_advantageTreeType");


                cb =new ClassB(tcid,f_className1,f_leader,f_tel);
                area =new Area(taid,f_areaName1,f_forestType,f_landType,f_advantageTreeType);
                cb.setArea(area);
                classList.add(cb);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }
        return classList;
    }

    @Override
    public int getCount(String f_className, String f_areaName) {
        int count=0;
        Connection conn =null;
        PreparedStatement ps =null;
        ResultSet rs =null;
        try {
            conn =DBUTil.getConn();
            String sql ="select count(0),f_className,f_areaName from t_class inner join t_area on t_class.pkid = t_area.pkid where 1=1 ";
            if(f_className!=null){
                sql+=" and f_className like '%"+f_className+"%' ";
            }
            if(f_areaName!=null){
                sql+=" and f_areaName like '%"+f_areaName+"%' ";
            }
            ps =conn.prepareStatement(sql);
            rs =ps.executeQuery();
            if(rs.next()){
                count =rs.getInt("count(0)");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }
        return count;
    }
}
